package com.ibilibili.utils;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

public class MysqlClient extends Exception{
    private String jdbcUrl = "";
    private String user = "";
    private String password = "";

    public MysqlClient(String jdbcUrl, String user, String password) throws Exception{
        this.jdbcUrl = jdbcUrl;
        this.user = user;
        this.password = password;
    }

    private Connection getConn() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(this.jdbcUrl, this.user, this.password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }


    public void write(String data[]) throws Exception{
        String sql = "insert into bili_crawl (aid, tid, tname, copyright, pic, title, attribute, duration, view, " +
                "danmaku, reply, favorite, coin, share, now_rank, his_rank, play, favorites, video_review, ctime, " +
                "mid, author, face) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = this.getConn();
            ps = conn.prepareStatement(sql);
            this.setData(ps, data);
            ps.executeUpdate();

        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if (null != ps) {
                ps.close();
            }

            if (null != conn) {
                conn.close();
            }
        }
    }

    public ArrayList select(String sql) throws Exception{
        ArrayList<HashMap<String, String>> result = new ArrayList <HashMap<String, String>>();
        Connection conn = null;
        try {
            conn = this.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String fields = Tools.substring(sql);
                String[] fieldArr = fields.split(",");
                HashMap<String, String> tmpHM = new HashMap<String, String>();
                for (String f: fieldArr) {
                    String value = rs.getString(f);
                    tmpHM.put(f, value);
                }
                result.add(tmpHM);
            }

        }catch (Exception e){
            e.printStackTrace();
        } finally {
            if (null != conn) {
                conn.close();
            }
        }
        return result;
    }

    public void setData(PreparedStatement ps, String [] data) throws Exception{
        ps.setInt(1, Integer.parseInt(data[0]));
        ps.setInt(2, Integer.parseInt(data[1]));
        ps.setString(3, data[2]);
        ps.setString(4, data[3]);
        ps.setString(5, data[4]);
        ps.setString(6, data[5]);
        ps.setInt(7, Integer.parseInt(data[6]));
        ps.setInt(8, Integer.parseInt(data[7]));
        ps.setInt(9, Integer.parseInt(data[8]));
        ps.setInt(10, Integer.parseInt(data[9]));
        ps.setInt(11, Integer.parseInt(data[10]));
        ps.setInt(12, Integer.parseInt(data[11]));
        ps.setInt(13, Integer.parseInt(data[12]));
        ps.setInt(14, Integer.parseInt(data[13]));
        ps.setInt(15, Integer.parseInt(data[14]));
        ps.setInt(16, Integer.parseInt(data[15]));
        ps.setInt(17, Integer.parseInt(data[16]));
        ps.setInt(18, Integer.parseInt(data[17]));
        ps.setInt(19, Integer.parseInt(data[18]));
        ps.setTimestamp(20, this.getTimeStamp(data[19]));
        ps.setInt(21, Integer.parseInt(data[20]));
        ps.setString(22, data[21]);
        ps.setString(23, data[22]);
    }

    public Timestamp getTimeStamp(String time) throws Exception{
        // 0000-00-00 00:00:00 时间格式插入到mysql会有异常
        String tmpTime = time;
        if (tmpTime == "0000-00-00 00:00:00" || tmpTime == "null" || "-1" == tmpTime || tmpTime.length() != 16) {
            tmpTime = "2000-01-01 00:00:00";
        }

        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        java.util.Date parsedDate = formatter.parse(tmpTime);
        return new Timestamp(parsedDate.getTime());
    }
}
